/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 *
 * @author aleX
 */
public class Query_Segreteria_Corsi {
 
    private static HashMap corsi; 
    
    public static List <Corso> get_corsi(String user)
    {    
       return (List <Corso>) corsi.get(user);   
    }
    
    public static List <Corso> query_lista_corsi (String user , String param_nome , String param_crediti , String param_docente , String param_periodo){
        
        if(corsi==null){
            corsi = new HashMap(250);  
        }
        
                
       List <Corso> result = new ArrayList <Corso> ();
       
      String query = "Select course_ID , notes , actual_start_date , institution , place , e.credits , course_name , planned_course_period , url ,first_name , last_name , email , count(student) as iscritti"
              + " from ( courses left join supervisors on professor = supervisor_ID )"
              + " left join courses_enrollments  e on course_ID = e.course  "
              + " where course_name like '"+param_nome+"%' AND planned_course_period like '"+param_periodo+"%' "
              + " group by course_ID "
              + " order by course_name ";
           
       try{
           
        Connection con = DBconnection.getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        Corso tmp;
            while(rs.next())     
             {
             tmp = new Corso();
             tmp.setAula_lezioni(rs.getString("place"));
             tmp.setData_inizio(rs.getString("actual_start_date"));
             tmp.setDocente(rs.getString("first_name")+" "+rs.getString("last_name"));
             tmp.setEmail(rs.getString("email"));
             tmp.setUrl_corso(rs.getString("url"));
             tmp.setNome(rs.getString("course_name"));
             tmp.setCrediti(rs.getString("credits"));
             tmp.setPeriodo(rs.getString("planned_course_period"));
             tmp.setNote(rs.getString("notes"));
             tmp.setNumero_iscritti(rs.getString("iscritti"));
             tmp.setId(rs.getInt("course_ID"));       
             result.add(tmp);    
             }   
        con.close();    
       }catch(Exception ex)
        {
            ex.printStackTrace();
        }
       corsi.put(user , result);
     return result;
    }
    
    public static void delete(String user)
          {
              if(corsi.containsKey(user))
            corsi.remove(user);
          }
    
  
    
}
    

